﻿using LdtJudge.DataEngine.CallInterface.ConfiCenter;
using System.Data;

namespace LdtJudge.DataEngine.Data
{
    public class SqlDALDataBaseStoresSYSTable
    {

        private static string connsys = ConfigRetrieval.GetConfigRetrieval.GetConnectionString("Ldt_JudgeDocSystem");
        private static SqlHandle sqhandle;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionstring"></param>

        public SqlDALDataBaseStoresSYSTable()
        {
            if (string.IsNullOrWhiteSpace(connsys))
            {
                connsys = connsys;
            }
            sqhandle = new SqlHandle(connsys);
        }
        /// <summary>
        /// 创建表的主键ID
        /// </summary>
        /// <param name="tablename">表名称</param>
        /// <returns></returns>
        public static string CreateTableKey(string tablename)
        {
            return sqhandle.CreateKeyID(tablename);
        }


        /// <summary>
        /// 获取表的主外键关系;
        /// Gets the table PFK relation.
        /// </summary>
        /// <returns></returns>
        public virtual DataTable GetTablePFKRelation()
        {
           
            string strSql = @"SELECT dict.Tbl_ID,
                                                   dict.Tbl_Name,
                                                   dict.Tbl_ParentTable,
                                                   dict.Tbl_FKColumn,
                                                   dic.Tbl_PKColumn 
                                                   FROM dbo.Dic_TableRelation dict
                                                   INNER JOIN dbo.Dic_Table dic
                                                   ON dic.Tbl_ID = dict.Tbl_ID
                                                   WHERE dict.Tbl_ParentTable IS NOT NULL ";
          
            return sqhandle.GetSqlHelper.ExecuteReader(strSql); 
        }

        

        /// <summary>
        /// 根据语句得到数据集合
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual DataTable GetTable_System(string sql)
        {

            return sqhandle.GetSqlHelper.ExecuteReader(sql);
        }
       
        /// <summary>
        /// 获取所有需要分析的表集合
        /// </summary>
        /// <param name="PreDataSource">前置数据源类型(为空时，则获取全部)</param>
        /// <returns></returns> 
        public virtual DataTable GetTable_EXTTable(string PreDataSource)
        {


            string strSQL;
            if (string.IsNullOrEmpty(PreDataSource))
            {
                strSQL = @"SELECT
	                                        Dic_Table.Tbl_ID,
	                                        Dic_Table.Tbl_Name,
	                                        Dic_Table.Tbl_NameChinese,
	                                        Dic_Table.Tbl_Remark,
	                                        Dic_Table.Tbl_PKColumn,
	                                        Dic_Table.Tbl_IsDocuLabelProperty,
	                                        Dic_Table.Tbl_SerialNumb,
	                                        Dic_Table.Tbl_NoteColumn,
	                                        Dic_Table.Tbl_OptimizeType,
	                                        Dic_TableRelation.Tbl_FKColumn,
	                                        Dic_TableRelation.Tbl_ParentTable
                                        FROM
	                                        Dic_Table
                                        INNER JOIN Dic_TableRelation ON Dic_Table.Tbl_ID = Dic_TableRelation.Tbl_ID
                                        ORDER BY
	                                        Dic_Table.Tbl_SerialNumb
                        ";
            }
            else
            {
                strSQL = @"SELECT
	                                    dtl.Tbl_ID,
	                                    dtl.Tbl_Name,
	                                    dtl.Tbl_NameChinese,
	                                    dtl.Tbl_Remark,
	                                    dtl.Tbl_PKColumn,
	                                    dtl.Tbl_IsDocuLabelProperty,
	                                    dtl.Tbl_SerialNumb,
	                                    dtl.Tbl_NoteColumn,
	                                    dtl.Tbl_OptimizeType,
	                                    dtr.Tbl_FKColumn,
	                                    dtr.Tbl_ParentTable
                                    FROM
	                                    Dic_Table AS dtl
                                    INNER JOIN Dic_TableRelation dtr ON dtl.Tbl_ID = dtr.Tbl_ID
                                    WHERE
	                                    (
		                                    dtl.Tbl_ID IN (
			                                    SELECT
				                                    col.Tbl_ID
			                                    FROM
				                                    Dic_TableColumn AS col
			                                    INNER JOIN Dic_TableColumn_Priority AS ex ON col.Col_ID = ex.Col_ID
			                                    WHERE
				                                    (ex.PT_Source = @PT_Source)
			                                    GROUP BY
				                                    col.Tbl_ID
		                                    )
	                                    )
                                    AND (
	                                    dtr.UseScene = 'AllSource'
	                                    OR CHARINDEX(@PT_Source, usescene, 0) > 0
                                    )
                                    AND dtr.Tbl_ParentTable IS NOT NULL
                                    ORDER BY
	                                    dtl.Tbl_SerialNumb
                        ";//AND dtr.Tbl_ParentTable IS NOT NULL 添加这个条件是因为在Dic_TableRelation表中CS_Case存在两条一样的数据。
            }
           
            return sqhandle.GetSqlHelper.ExecuteReader(strSQL);
        }

        /// <summary>
        /// 获取表的系统配置
        /// </summary>
        /// <returns></returns>
        public virtual DataTable GetTable_SysConfig()
        {
            string strSQL = @"SELECT  dict.Tbl_ID ,
                                                    dict.Tbl_Name ,
                                                    dict.Tbl_NameChinese ,
                                                    dict.Tbl_ParentTable ,
                                                    dict.Tbl_PKColumn ,
                                                    dict.Tbl_FKColumn ,
                                                    dictc.Col_ID,
		                                            dictc.Col_Name,
		                                            dictc.Col_NameChinese,
		                                            dictc.Col_Type,
		                                            dictc.Col_Length,
		                                            dictc.Col_IsPK
                                            FROM    dbo.Dic_TableColumn dictc
                                                    LEFT JOIN dbo.Dic_Table dict ON dict.Tbl_ID = dictc.Tbl_ID";
            return sqhandle.GetSqlHelper.ExecuteReader(strSQL);
        }


        /// <summary>
        /// 获取所有的表的列和规则集合
        /// Gets the table_ ext table column rule.
        /// </summary>
        /// <param name="PTSource">如果没有文书类型则为获取全部的数据列和规则</param>
        /// <returns>DataTable.</returns>
        public virtual DataTable GetTable_EXTTableColumnRule(string PTSource)
        {
          
            string strsqlnew = @"SELECT
	                                            col.Col_ID, col.Col_Name, col.Col_NameChinese, col.Tbl_ID, col.Col_Type, col.Col_Length,col.Col_IsPK,
	                                            col.Col_IsDocuLabelProperty, col.SourceType, col.SourceContent, col.Col_SerialNumb, col.Col_IsSerialField,
	                                            col.Col_DefaultValue, col.Col_FormatType, col.Col_IsIdentity, col.Col_IsDefineParse, col.Col_DefineParseClassName,
	                                            col.MatchKeyWord, col.IsCommonFields,
	                                            ex.PT_Level, ex.PT_Source, ex.PT_IsParagraphExtract, ex.PT_SourceParagraph, ex.PT_RelatRegexInfoCode,
	                                            ex.PT_IsDefineExtract, ex.PT_IsRelatParticpant, ex.IsRelatCaseCause, ex.ElementName
                                            FROM
	                                            Dic_TableColumn AS col
                                            LEFT OUTER JOIN ( SELECT    *   FROM  Dic_TableColumn_Priority WHERE  PT_Source = '" + PTSource + "') AS ex ON col.Col_ID = ex.Col_ID";


            return sqhandle.GetSqlHelper.ExecuteReader(strsqlnew); ; ;

        }

        /// <summary>
        /// 获取分段配置集合
        /// Gets the table_ ext table paragraph.
        /// </summary>
        /// <param name="CT_Code">案件类型a1、b1 等The c t_ code.</param>
        /// <param name="CD_Code">文书类型编号The c d_ code.</param>
        /// <returns>DataTable.</returns>
        public virtual DataTable GetTable_ExtTableParagraph(string CT_Code, string CD_Code)
        {
            string strsql = @"select t.CT_Name,t.CT_Code,b.CD_Name,b.[CD_CODE],b.CD_ID,c.CDL_ID,c.CDL_ParentID,c.CDL_Name,C.CDL_Code,c.[CDL_Level],c.CDL_ShowIndex,c.CDL_RelatRegexInfoCode from SYS_CaseType t 
            left join SYS_CaseTypeDocRel r on t.CT_Code=r.CT_Code
            left join SYS_CaseType_DocType b on r.CD_ID=b.CD_ID
            left join SYS_CaseType_DocType_LogicParag c on c.CD_ID=b.[CD_ID]  Where 1=1";
            if (!string.IsNullOrEmpty(CT_Code))
            {//如果案件类型不为空，或者说审计不为空
                strsql += "  and t.CT_Code='" + CT_Code + "'";
            }
            if (!string.IsNullOrEmpty(CD_Code))
            {//如果文书的编号不为空
                strsql += "  and  b.CD_Code='" + CD_Code + "'";
            }
            strsql += " ORDER BY c.CDL_ShowIndex";

            return sqhandle.GetSqlHelper.ExecuteReader(strsql);
        }

       

        /// <summary>
        /// 数据表配置信息
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual DataTable GetTabel_SysExtrcat(string where)
        {
            string sql = @"SELECT tab.*,col.*,cop.* FROM dbo.Dic_Table tab ,dbo.Dic_TableColumn col,dbo.Dic_TableColumn_Priority cop WHERE
                           tab.Tbl_ID=col.Tbl_ID AND col.Col_ID=cop.Col_ID ";
           
            return sqhandle.GetSqlHelper.ExecuteReader(sql); ;
        }


       
    
    }
}
